package team02.server.bean;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import team02.client.core.test.Examinee;
import team02.client.core.test.Test;
import team02.server.model.MySQLModel;

public class ExamineeBean extends MySQLModel {

	public ExamineeBean(Connection connection) {
		super(connection);
		this.examinee = new Examinee();
	}

	public ExamineeBean() {
		super(null);
		this.examinee = new Examinee();
	}

	public static final String FIELD_NAME = "" + "  `id`," + //1
			"  `test_id`," + //2
			"  `name`," + //3
			"  `username`," + //4 
			"  `password`," + //5
			"  `score`," + //6
			"  `marked`," + //7
			"  `score2`," + //8
			"  `submitted`," + //9
			"  `score_100`"; //10

	Examinee examinee;

	@Override
	public int load() throws SQLException {
		String queryString = "SELECT " + FIELD_NAME + " FROM " + TABLE_EXAMINEE;
		if (examinee.getId() != 0) {
			queryString += " WHERE `id`=" + examinee.getId();
		} else if (examinee.getTestId() != 0 && examinee.getUsername() != null && examinee.getPassword() != null) {
			queryString += " WHERE `test_id`=" + examinee.getTestId() + " AND `username` = '" + examinee.getUsername()
					+ "' AND `password` = '" + examinee.getPassword() + "'";
		} else {
			return 1;
		}
		initConnection();
		executeQuery(queryString);
		if (resultSet.next()) {
			loadExaminee(examinee, resultSet);
			freeResources();
			return 0;
		} else {
			freeResources();
			return 1;
		}
	}

	private void loadExaminee(Examinee ex, ResultSet resultSet) throws SQLException {
		ex.setId(resultSet.getInt(1));
		ex.setTestId(resultSet.getInt(2));
		ex.setName(resultSet.getString(3));
		ex.setUsername(resultSet.getString(4));
		ex.setPassword(resultSet.getString(5));
		ex.setScore(resultSet.getInt(6));
		ex.setMarked(resultSet.getBoolean(7));
		ex.setScore2(resultSet.getInt(8));
		ex.setSubmitted(resultSet.getBoolean(9));
		ex.setScore100(resultSet.getInt(10));
	}

	@Override
	public int save() throws SQLException {
		throw new UnsupportedOperationException("Khong dung ham nay");
	}

	@Override
	public int insert() throws SQLException {
		String updateString = "INSERT INTO "
				+ TABLE_EXAMINEE
				+ "(`test_id`, `name`, `username`, `password`, `score`, `marked`, `score2`, `submitted`, `score_100`) VALUE("
				+ examinee.getTestId() + ",'" + examinee.getName() + "','" + examinee.getUsername() + "','"
				+ examinee.getPassword() + "'," + examinee.getScore() + "," + examinee.isMarked() + ","
				+ examinee.getScore2() + "," + examinee.isSubmitted() + "," + examinee.getScore100() + ")";
		try {
			initConnection();
			int rs = executeUpdate(updateString);
			if (rs > 0) {
				return 0;
			} else {
				return 1;
			}
		} finally {
			freeResources();
		}
	}

	@Override
	public int delete() throws SQLException {
		throw new UnsupportedOperationException("Khong dung ham nay");
	}

	@Override
	public void clear() {
		examinee.setId(0);
		examinee.setName(null);
		examinee.setPassword(null);
		examinee.setScore(0);
		examinee.setTestId(0);
		examinee.setUsername(null);
		examinee.setScore2(0);
		examinee.setSubmitted(false);
		examinee.setMarked(false);
		examinee.setScore100(0);
	}

	public ArrayList<Examinee> listAllExaminees(Test test) throws SQLException {
		String queryString = "SELECT " + FIELD_NAME + " FROM " + TABLE_EXAMINEE + " WHERE `test_id`=" + test.getId();

		try {
			ArrayList<Examinee> examineeList = new ArrayList<Examinee>();
			initConnection();
			executeQuery(queryString);
			while (resultSet.next()) {
				Examinee ex = new Examinee();
				loadExaminee(ex, resultSet);
				examineeList.add(ex);
			}
			return examineeList;
		} finally {
			freeResources();
		}
	}
	public ArrayList<Examinee> listAllSubmittedExaminees(Test test) throws SQLException {
		String queryString = "SELECT " + FIELD_NAME + " FROM " + TABLE_EXAMINEE + " WHERE `test_id`=" + test.getId()+" AND submitted='1'";

		try {
			ArrayList<Examinee> examineeList = new ArrayList<Examinee>();
			initConnection();
			executeQuery(queryString);
			while (resultSet.next()) {
				Examinee ex = new Examinee();
				loadExaminee(ex, resultSet);
				examineeList.add(ex);
			}
			return examineeList;
		} finally {
			freeResources();
		}
	}
	/* - Getter and Setter - */

	public int getId() {
		return examinee.getId();
	}

	public void setId(int id) {
		examinee.setId(id);
	}

	public String getUsername() {
		return examinee.getUsername();
	}

	public void setUsername(String username) {
		examinee.setUsername(username);
	}

	public String getName() {
		return examinee.getName();
	}

	public void setName(String name) {
		examinee.setName(name);
	}

	public String getPassword() {
		return examinee.getPassword();
	}

	public void setPassword(String password) {
		examinee.setPassword(password);
	}

	public Examinee getExaminee() {
		return examinee;
	}

	public void setExaminee(Examinee examinee) {
		this.examinee = examinee;
	}

	public int getTestId() {
		return examinee.getTestId();
	}

	public void setTestId(int testId) {
		examinee.setTestId(testId);
	}

	public boolean isSubmitted() {
		return examinee.isSubmitted();
	}

	public void setSubmitted(boolean isSubmitted) {
		examinee.setSubmitted(isSubmitted);
	}
	
}
